package com.chima.dao;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.hibernate.Session;
import org.hibernate.criterion.DetachedCriteria;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.chima.domain.Employees;
import com.chima.domain.MiddleEmpRate;
import com.chima.domain.Rate;

public class RateDao extends HibernateDaoSupport{


	//分页查询
		public List<Rate> findByCriteria(DetachedCriteria criteria, int firstResult,int maxResults) {
			return this.getHibernateTemplate().findByCriteria(criteria,firstResult,maxResults);
		}
	   //查找总页数
		public int findByTotalCount(String date, int dept) {
			long temp=2312312l;
			if(dept==0){
				if(date==null || "".equals(date)){
					 SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM"); 
			         Calendar   calendar=Calendar.getInstance(); 
			         calendar.setTime(new Date());
			         calendar.set(Calendar.MONTH,calendar.get(Calendar.MONTH)); 
			         date= sdf.format(calendar.getTime());
			         List list= this.getHibernateTemplate().find("select count(*) from Rate where date='"+date+"' ");
					 temp = (Long) list.get(0);
				}else if(date!=null){
					List list= this.getHibernateTemplate().find("select count(*) from Rate where date='"+date+"'");
					 temp = (Long) list.get(0);
				}
			}else if (dept!=0){
				if(date==null || "".equals(date)){
					 SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM"); 
			         Calendar   calendar=Calendar.getInstance(); 
			         calendar.setTime(new Date());
			         calendar.set(Calendar.MONTH,calendar.get(Calendar.MONTH)); 
			         date= sdf.format(calendar.getTime());
			         List list= this.getHibernateTemplate().find("select count(*) from Rate where date='"+date+"' and department.id="+dept+"");
					 temp = (Long) list.get(0);
				}	else if(date!=null){	 
					  List list= this.getHibernateTemplate().find("select count(*) from Rate where date='"+date+"' and department.id="+dept+"");
					 temp = (Long) list.get(0);
				}
			}
			
			return (int) temp;
		}
		public void updateGrade(Double grade ,int  id) {
			String hql=" update Rate r set r.grade=? where r.id=?";
			Session session=this.getSession();
			session.createQuery(hql).setParameter(0,grade ).setParameter(1,id).executeUpdate();
			super.releaseSession(session);
		}
		public Rate findById( int id ) {
			Session session=this.getSession();
			Rate r = (Rate)session.createQuery("from Rate e where e.id = ?").setParameter(0, id).uniqueResult();
			super.releaseSession(session);
			return r;
		}
		public List<Rate>  findByDate( String date ) {
			Session session=this.getSession();
			List<Rate>  r = session.createQuery("from Rate e where e.date = ?").setParameter(0, date).list();
			super.releaseSession(session);
			return r;
		}
		public void save(Rate rate) {
			this.getHibernateTemplate().save(rate);
		}
		public List<Rate>  findByDateAndDepart( String date, int depart ) {
			Session	session= this.getSession();
			List<Rate>  r = session.createQuery("from Rate e where e.date = ?and e.department.id=? ").setParameter(0, date).setParameter(1, depart).list();
			super.releaseSession(session);
			return r;
		}
		public List<Rate>  findByDepart(  int depart ) {
			Session	session= this.getSession();
			List<Rate>  r = session.createQuery("from Rate e where e.department.id=? ").setParameter(0, depart).list();
			super.releaseSession(session);
			return r;
		}
		public Rate findByDateAndEmp(String date, Employees emp) {
			Session	session= this.getSession();
			Rate r = (Rate) session.createQuery("from Rate e where e.date = ?and e.employee=? ").setParameter(0, date).setParameter(1, emp).uniqueResult();
			super.releaseSession(session);
			return r;
		}
		
		//分页查询
		public List<Rate> findMiddleEmpRateByCriteria(DetachedCriteria criteria, int firstResult,int maxResults) {
			return this.getHibernateTemplate().findByCriteria(criteria,firstResult,maxResults);
		}
		
		public int findMiddleTotalCount(String date) {
			  long temp=2312312l;
			  List list= this.getHibernateTemplate().find("select count(*) from Rate where date='"+date+"' and position=2 ");
			  temp = (Long) list.get(0);
			  return (int) temp;
			
		}
		public void updateMiddleGrade(Double grade ,int  id) {
			String hql=" update MiddleEmpRate r set r.grade=? where r.id=?";
			Session session=this.getSession();
			session.createQuery(hql).setParameter(0,grade ).setParameter(1,id).executeUpdate();
			super.releaseSession(session);
		}
		public MiddleEmpRate findByEmpNameAnddate(String name, String date) {
			
			String hql=" from MiddleEmpRate r  where r.employee=? and r.date=?";
			Session session=this.getSession();
			MiddleEmpRate m=(MiddleEmpRate) session.createQuery(hql).setParameter(0,name).setParameter(1,date).uniqueResult();
			super.releaseSession(session);
			return m;
			
		}
		public List<Rate> findMiddleEmpRateByDate(String date,int position) {
			
			String hql=" from Rate r  where r.date=? and  position=?";
			Session session=this.getSession();
			List<Rate> m= session.createQuery(hql).setParameter(0,date).setParameter(1,position).list();
			super.releaseSession(session);
			return m;
		}
		public List<MiddleEmpRate> findMiddleEmpRateByDateAndDept(String date,String dept) {
			
			String hql="from MiddleEmpRate r  where r.date=? and r.deptId in ("+dept+") ";
			Session session=this.getSession();
			List<MiddleEmpRate> ob= session.createQuery(hql).setParameter(0,date).list();
			super.releaseSession(session);
			return ob;
		}
		
//		public List<MiddleEmpRate> findMiddleEmpRateByDateAndDept(String date,String dept) {
//			List<MiddleEmpRate> Mid=new ArrayList();
//			String hql=" select id,grade,department,employee,deptId,date from t_middleemprate r  where r.date=? and deptId in "+dept+" ";
//			Session session=this.getSession();
//			List<Object[]> ob= session.createQuery(hql).setParameter(0,date).list();
//			if(ob.size()>0){
//				for(int i=0;i<ob.size();i++){
//					MiddleEmpRate mm =new MiddleEmpRate();
//					mm.setId(Integer.parseInt(ob.get(i)[0].toString()));
//					mm.setGrade(Double.valueOf(ob.get(i)[1].toString()) );
//					mm.setDepartment(ob.get(i)[2].toString());
//					mm.setEmployee(ob.get(i)[3].toString());
//					mm.setDeptId(ob.get(i)[4].toString());
//					mm.setDate(ob.get(i)[5].toString());
//					Mid.add(mm);
//				}
//			}
//			super.releaseSession(session);
//			return Mid;
//		}
		public void saveMiddleEmpRate (MiddleEmpRate m) {
			this.getHibernateTemplate().save(m);
		}
		
		public void updateWorkDay(int workday ,int  id) {
			String hql=" update Rate r set r.actualworkday=? where r.id=?";
			Session session=this.getSession();
			session.createQuery(hql).setParameter(0,workday ).setParameter(1,id).executeUpdate();
			super.releaseSession(session);
		}
		
		public void deleterate(int empid) {
			
			 Session session=this.getSession();
			 String sql1=" delete  from t_rate where empID=?";
			 session.createSQLQuery(sql1).setParameter(0, empid).executeUpdate();
			 super.releaseSession(session);
		}
}
